/*
 * To change this rs.getString(index++)late, choose Tools | rs.getString(index++)lates
 * and open the rs.getString(index++)late in the editor.
 */

package DAO;

import DTO.MyMailDTO;
import doanlythuyet_javaoutlook.MyEmum.JEnum_TinhTrang;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;

/**
 *
 * @author Dang Thi Phuong Thao
 */
public class MyMailDAO extends AbDAO {
    /**
     * Chuyen mot ResultSet thanh mot arraylist cac MyMailDTO
     * @param rs ResultSet
     * @return Arraylist duoc tao
     * sau khi goi cau truy van chi can dung ham nay de chuyen doi ket qua, coi them o ham
     * layTatCaMail de biet cach su dung
     */
    private ArrayList<MyMailDTO> capNhatTuResultSet(ResultSet rs){
        ArrayList<MyMailDTO> Kq = new ArrayList<MyMailDTO>();
        /*String Chuoi = "";
        try {
            for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                Chuoi += rs.getMetaData().getColumnLabel(i) + ", ";
            }
            JOptionPane.showMessageDialog(null, Chuoi);
        } catch (SQLException ex) {
            Logger.getLogger(MyMailDAO.class.getName()).log(Level.SEVERE, null, ex);
        }*/
        try {
            //index = rsmd.getColumnCount();
            int index = 0;
            while (rs.next()) {
                index = 1;
                //String temp = rs.getString(index++);
                int Id = rs.getInt(index++);
                String ChuDe = rs.getString(index++);
                String NguoiGoi = rs.getString(index++);

                DateFormat df=new SimpleDateFormat("dd-MM-yyyy hh:mm:ss");
                Date Ngay = df.parse(rs.getString(index++));
                
                int MucDo = rs.getInt(index++);
                String DuongDanThuMuc = rs.getString(index++);
                String DuongDanFileChuaNoiDung = rs.getString(index++);
                int TinhTrang = rs.getInt(index++);
                String NguoiNhan = rs.getString(index++);
                
                String CC = rs.getString(index++);
                if (CC == null)
                    CC = "";

                String BCC = rs.getString(index++);
                if (BCC == null)
                    BCC = "";
                
                MyMailDTO mailDTO = new MyMailDTO(Id, ChuDe, NguoiGoi, NguoiNhan, MucDo,
                        TinhTrang, CC, BCC, Ngay, DuongDanThuMuc, DuongDanFileChuaNoiDung);
                Kq.add(mailDTO);
            }
        } catch (ParseException ex) {
            Logger.getLogger(MyMailDAO.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(null, ex.getMessage());
        } catch (SQLException ex) {
            Logger.getLogger(MyMailDTO.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(null, ex.getMessage());
        }
        return Kq;
    }

    public ArrayList<MyMailDTO> layTatCaMail () throws IOException{
        KetNoi();

        ResultSet rs = thucThiTraVeResultSet("select * from Mail");
        ArrayList<MyMailDTO> Kq = capNhatTuResultSet(rs);

        DongKetNoi();
        
        return Kq;
    }
    public int themMail (MyMailDTO mailDTO) throws IOException{
        KetNoi();

        DateFormat df=new SimpleDateFormat("dd-MM-yyyy hh:mm:ss");
        String date = df.format(mailDTO.getM_Ngay());
        //String date = mailDTO.getM_Ngay().toString().substring(0, "dd-MM-yyyy hh:mm:ss".length());
        String sql = "insert into Mail(ChuDe, NguoiGui, Ngay, MucDo, DuongDanThuMuc, DuongDanFileChuaNoiDung, TinhTrang, NguoiNhan, CC, BCC)" +
                " values(" + "'" + mailDTO.getM_ChuDe() + "', " + "'" + mailDTO.getM_NguoiGoi() + "', " + "'" + date + "', " +
                mailDTO.getM_MucDo() + ", " + "'" + mailDTO.getM_DuongDanThuMuc() + "', " + "'" + mailDTO.getM_DuongDanFileChuaNoiDung() + "', " + 
                mailDTO.getM_TinhTrang() + ", " + "'" + mailDTO.getM_NguoiNhan() + "', " + "'" + mailDTO.getM_CC() + "', " +
                "'" + mailDTO.getM_BCC() +  "'" + ")";
        int Kq = thucThiUpdate(sql);

        DongKetNoi();

        return Kq;
    }
    /**
     * Xoa mot mail
     * @param mailDTO
     * @return
     * @throws java.io.IOException
     */
    public int xoaMail(MyMailDTO mailDTO) throws IOException{
        KetNoi();
        String sql = "Delete from Mail Where ID ="+ mailDTO.getM_Id();
        int kq = thucThiUpdate(sql);
        DongKetNoi();
        return kq;
    }

    public ArrayList<MyMailDTO> layTatCaMailTrongThuMuc (String duongDan) throws IOException{
        KetNoi();

        ResultSet rs = thucThiTraVeResultSet("select * from Mail where duongdanthumuc = '" + duongDan + "'");
        ArrayList<MyMailDTO> Kq = capNhatTuResultSet(rs);

        DongKetNoi();

        return Kq;
    }
    public ArrayList<MyMailDTO> layTatCaMailVoiDieuKien (String duongDan, Hashtable<String, String> cacDieuKien) throws IOException{
        KetNoi();

        String whereString = " where TinhTrang <>" + JEnum_TinhTrang.DaXoa.value() + " and ";
        Enumeration<String> e = cacDieuKien.keys();
        while(e.hasMoreElements()){
            String key = e.nextElement();
            whereString += " " + key + "=";
            if (!key.equalsIgnoreCase("TinhTrang") && !key.equalsIgnoreCase("MucDo") && !key.equalsIgnoreCase("ID")){
                whereString += "'";
            }
            if (!key.equalsIgnoreCase("Ngay"))
                whereString += cacDieuKien.get(key);
            else{
                DateFormat df=new SimpleDateFormat("dd-MM-yyyy hh:mm:ss");
                whereString += df.format(cacDieuKien.get(key));
            }
            if (whereString.contains("'"))
                whereString +="'";
            whereString += " and ";
        }
        if (whereString.contains(" and"))
            whereString = whereString.substring(0, whereString.lastIndexOf(" and "));
        //JOptionPane.showMessageDialog(null, whereString);

        String sql = "select * from Mail " + whereString;
        ResultSet rs = thucThiTraVeResultSet(sql);
        ArrayList<MyMailDTO> Kq = capNhatTuResultSet(rs);

        DongKetNoi();

        return Kq;
    }
    public int capNhatMail (MyMailDTO mailDTO) throws IOException{
        KetNoi();

        DateFormat df=new SimpleDateFormat("dd-MM-yyyy hh:mm:ss");
        String date = df.format(mailDTO.getM_Ngay());
        int Kq = 0;
        String sql = "update Mail set ChuDe = '" + mailDTO.getM_ChuDe() + "', NguoiGui = '" + mailDTO.getM_NguoiGoi() + "'," +
                " Ngay = '" + date + "', MucDo = " + mailDTO.getM_MucDo() + ", DuongDanThuMuc = '" + mailDTO.getM_DuongDanThuMuc() + "', " +
                "DuongDanFileChuaNoiDung = '" + mailDTO.getM_DuongDanFileChuaNoiDung() + "', " + "TinhTrang = " + mailDTO.getM_TinhTrang() + " , " +
                "NguoiNhan = '" + mailDTO.getM_NguoiGoi() + "', CC ='" + mailDTO.getM_NguoiGoi() + "', BCC = '" + mailDTO.getM_NguoiGoi() + "'";
        sql+=" where id = " + mailDTO.getM_Id();
        Kq = thucThiUpdate(sql);
        DongKetNoi();

        return Kq;
    }
}
